using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Text;
using VisualDB.Entities;
using System.Collections.Generic;

namespace VisualDB.DAL
{
    public class BaseDAL
    {
        protected string GenerateScriptTable(ScriptType scriptType, TableEntity table, List<ColumnEntity> columns, string parameterSymbol)
        {
            StringBuilder sb = new StringBuilder();
            bool isFirst;

            switch (scriptType)
            {
                case ScriptType.Select:
                case ScriptType.SelectAll:
                    sb.Append("Select ");

                    isFirst = true;

                    foreach (ColumnEntity column in columns)
                    {
                        if (!isFirst)
                            sb.Append(",");
                        else
                            isFirst = false;

                        sb.Append(column.Name);

                    }

                    sb.AppendFormat(" from {0}", table.Name);

                    if (scriptType == ScriptType.Select)
                    {
                        sb.Append(" where ");

                        isFirst = true;

                        foreach (ColumnEntity column in columns)
                        {
                            if (column.PrimaryKey)
                            {
                                if (!isFirst)
                                    sb.Append(",");
                                else
                                    isFirst = false;

                                sb.AppendFormat("{0}={1}{0}", column.Name, parameterSymbol);
                            }
                        }
                    }

                    break;

                case ScriptType.Insert:
                    sb.AppendFormat("Insert into {0}(", table.Name);

                    isFirst = true;

                    foreach (ColumnEntity column in columns)
                    {
                        if (!isFirst)
                            sb.Append(",");
                        else
                            isFirst = false;

                        sb.Append(column.Name);

                    }

                    sb.Append(") values (");

                    isFirst = true;

                    foreach (ColumnEntity column in columns)
                    {
                        if (!isFirst)
                            sb.Append(",");
                        else
                            isFirst = false;

                        sb.Append(parameterSymbol + column.Name);
                    }

                    sb.Append(")");

                    break;

                case ScriptType.Update:
                    sb.AppendFormat("Update {0} Set ", table.Name);

                    isFirst = true;

                    foreach (ColumnEntity column in columns)
                    {
                        if (!isFirst)
                            sb.Append(",");
                        else
                            isFirst = false;

                        sb.AppendFormat("{0}={1}{0}", column.Name, parameterSymbol);
                    }

                    sb.Append(" where ");

                    isFirst = true;

                    foreach (ColumnEntity column in columns)
                    {
                        if (column.PrimaryKey)
                        {
                            if (!isFirst)
                                sb.Append(",");
                            else
                                isFirst = false;

                            sb.AppendFormat("{0}={1}{0}", column.Name, parameterSymbol);
                        }
                    }

                    break;

                case ScriptType.Delete:
                    sb.AppendFormat("Delete from {0} where ", table.Name);

                    isFirst = true;

                    foreach (ColumnEntity column in columns)
                    {
                        if (column.PrimaryKey)
                        {
                            if (!isFirst)
                                sb.Append(",");
                            else
                                isFirst = false;

                            sb.AppendFormat("{0}={1}{0}", column.Name, parameterSymbol);
                        }
                    }

                    break;
            }

            return sb.ToString();
        }

        protected string GenerateScriptSelectView(ViewEntity view, List<ColumnEntity> columns)
        {
            StringBuilder sb = new StringBuilder();
            bool isFirst = true;

            sb.Append("Select ");

            isFirst = true;

            foreach (ColumnEntity column in columns)
            {
                if (!isFirst)
                    sb.Append(",");
                else
                    isFirst = false;

                sb.Append(column.Name);
            }

            sb.AppendFormat(" from {0}", view.Name);

            return sb.ToString();
        }

        private static int CompareColumns(ColumnEntity column1, ColumnEntity column2)
        {
            return column1.Id.CompareTo(column2.Id);
        }

        protected string GenerateScriptSaveTable(DatabaseType databaseType, bool newTable, TableEntity table, List<ColumnEntity> originalColumns, List<ColumnEntity> newColumns, string addColumnCommand, string changeColumnCommand, string dropColumnCommand)
        {
            StringBuilder sb = new StringBuilder();

            if (newTable)
            {
                StringBuilder sbPrimaryKeys = new StringBuilder();

                sb.AppendFormat("CREATE TABLE {0}(", table.Name);

                bool isFirstColumn = true;
                bool isFirstKey = true;

                foreach (ColumnEntity column in newColumns)
                {
                    if (isFirstColumn)
                        isFirstColumn = false;
                    else
                        sb.Append(",");

                    string allowNulls = column.AllowNulls ? "" : "NOT NULL";

                    sb.AppendFormat("{0} {1} {2}", column.Name, column.Type, allowNulls);

                    if (column.PrimaryKey)
                    {
                        if (isFirstKey)
                            isFirstKey = false;
                        else
                            sbPrimaryKeys.Append(",");

                        sbPrimaryKeys.AppendFormat("PRIMARY KEY ({0})", column.Name);
                    }
                }

                sb.Append(",");
                sb.Append(sbPrimaryKeys.ToString());
                sb.Append(")");
            }
            else
            {
                originalColumns.Sort(CompareColumns);
                newColumns.Sort(CompareColumns);

                int origNCol = 0;
                int newNCol = 0;
                bool end = false;

                while (!end)
                {
                    ColumnEntity originalColumn;
                    ColumnEntity newColumn;

                    if (origNCol == originalColumns.Count)
                    {
                        newColumn = newColumns[newNCol];
                        string allowNulls = newColumn.AllowNulls ? "" : "NOT NULL";

                        sb.AppendFormat("ALTER TABLE {0} {1} {2} {3} {4};\r\n", table.Name, addColumnCommand, newColumn.Name, newColumn.Type, allowNulls);
                        newNCol++;
                    }
                    else if (newNCol == newColumns.Count)
                    {
                        originalColumn = originalColumns[origNCol];
                        sb.AppendFormat("ALTER TABLE {0} {1};\r\n", dropColumnCommand, originalColumn.Name);
                        origNCol++;
                    }
                    else
                    {
                        originalColumn = originalColumns[origNCol];
                        newColumn = newColumns[newNCol];

                        string allowNulls = newColumn.AllowNulls ? "" : "NOT NULL";

                        if (newColumn.Id == originalColumn.Id)
                        {
                            if (newColumn.Name != originalColumn.Name || newColumn.Type != originalColumn.Type ||
                                newColumn.AllowNulls != originalColumn.AllowNulls || newColumn.PrimaryKey != originalColumn.PrimaryKey)
                            {
                                if (databaseType.Name == DatabaseType.SQLServer)
                                {
                                    if (originalColumn.Name != newColumn.Name)
                                        sb.AppendFormat("EXECUTE sp_rename N'{0}.{1}', N'{2}', 'COLUMN';\r\n", table.Name, originalColumn.Name, newColumn.Name);

                                    sb.AppendFormat("ALTER TABLE {0} {1} {2} {3} {4};\r\n", table.Name, changeColumnCommand, newColumn.Name, newColumn.Type, allowNulls);
                                }
                                else
                                {
                                    sb.AppendFormat("ALTER TABLE {0} {1} {2} {3} {4} {5};\r\n", table.Name, changeColumnCommand, originalColumn.Name, newColumn.Name, newColumn.Type, allowNulls);
                                }
                            }

                            origNCol++;
                            newNCol++;
                        }
                        else if (newColumn.Id.CompareTo(originalColumn.Id) > 0)
                        {
                            sb.AppendFormat("ALTER TABLE {0} {1} {2};\r\n", table.Name, dropColumnCommand, originalColumn.Name);
                            origNCol++;
                        }
                        else
                        {
                            sb.AppendFormat("ALTER TABLE {0} {1} {2} {3} {4};\r\n", table.Name, addColumnCommand, newColumn.Name, newColumn.Type, allowNulls);
                            newNCol++;
                        }
                    }

                    if ((origNCol == originalColumns.Count) && newNCol == newColumns.Count)
                    {
                        end = true;
                    }
                }

                if (sb.Length > 0)
                    sb.Remove(sb.Length - 1, 1);
            }

            return sb.ToString();
        }
    }
}
